package achievement.dao.Impl;

import achievement.dao.SzlRewardDao;
import achievement.domain.SzlReward;
import cn.hutool.core.util.StrUtil;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SzlRewardDaoImpl implements SzlRewardDao {
    private Connection con;
    private PreparedStatement psmt;
    private ResultSet rs;
    @Override
    public void insert(SzlReward szlReward) {
        String sql = "insert into szl_reward\n" +
                "        (reward_name,reward_level,reward_grade,reward_rank,reward_time,reward_proof) \n" +
                "values(?,?,?,?,?,?)";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1,szlReward.getRewardName());
            psmt.setString(2,szlReward.getRewardLevel());
            psmt.setString(3,szlReward.getRewardGrade());
            psmt.setInt(4,szlReward.getRewardRank());
            psmt.setObject(5,szlReward.getRewardTime());
            psmt.setString(6,szlReward.getRewardProof());
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public void updateById(SzlReward szlReward) {
        String sql = "update szl_reward set";
        List<Object> param = new ArrayList<>();
        if (szlReward == null){
            return;
        }else{
            if(szlReward.getRewardId() != null){
                sql += " reward_id = ?,";
                param.add(szlReward.getRewardId());
            }
            if(szlReward.getRewardName() != null){
                sql += " reward_name = ?,";
                param.add(szlReward.getRewardName());
            }
            if(szlReward.getType() != null){
                sql += " type = ?,";
                param.add(szlReward.getType());
            }
            if(szlReward.getRewardLevel() != null){
                sql += " reward_level = ?,";
                param.add(szlReward.getRewardLevel());
            }
            if(szlReward.getRewardGrade() != null){
                sql += " reward_grade = ?,";
                param.add(szlReward.getRewardGrade());
            }
            if(szlReward.getRewardRank() != null){
                sql += " reward_rank = ?,";
                param.add(szlReward.getRewardRank());
            }
            if(szlReward.getRewardTime() != null){
                sql += " reward_time = ?,";
                param.add(szlReward.getRewardTime());
            }
            if(szlReward.getRewardProof() != null){
                sql += " reward_proof = ?,";
                param.add(szlReward.getRewardProof());
            }
        }
        sql = StrUtil.strip(sql,null,",");
        sql+=" where reward_id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            psmt.setLong(param.size()+1,szlReward.getRewardId());
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public void deleteById(Long id) {
        String sql = "delete from szl_reward where reward_id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1,id);
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public SzlReward selectById(Long id) {
        String sql = "select * from szl_reward where reward_id = ?";
        SzlReward szlReward = new SzlReward();
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1,id);
            rs = psmt.executeQuery();
            while (rs.next()){
                szlReward.setRewardId(rs.getLong(1));
                szlReward.setRewardName(rs.getString(2));
                szlReward.setType(rs.getInt(3));
                szlReward.setRewardLevel(rs.getString(4));
                szlReward.setRewardGrade(rs.getString(5));
                szlReward.setRewardRank(rs.getInt(6));
                szlReward.setRewardTime(rs.getDate(7));
                szlReward.setRewardProof(rs.getString(8));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return szlReward;
    }

    @Override
    public List<SzlReward> selectList(SzlReward szlReward) {
        String sql = "select * from szl_reward where 1=1";
        List<SzlReward> list = new ArrayList<>();
        List<Object> param = new ArrayList<>();
        if (szlReward == null){
            sql = "select * from szl_reward";
        }else{
            if(szlReward.getRewardId() != null){
                sql += " and reward_id = ?";
                param.add(szlReward.getRewardId());
            }
            if(szlReward.getRewardName() != null){
                sql += " and reward_name like \"%\"?\"%\"";
                param.add(szlReward.getRewardName());
            }
            if(szlReward.getType() != null){
                sql += " and type = ?";
                param.add(szlReward.getType());
            }
            if(szlReward.getRewardLevel() != null){
                sql += " and reward_level like \"%\"?\"%\"";
                param.add(szlReward.getRewardLevel());
            }
            if(szlReward.getRewardGrade() != null){
                sql += " and reward_grade like \"%\"?\"%\"";
                param.add(szlReward.getRewardGrade());
            }
            if(szlReward.getRewardRank() != null){
                sql += " and reward_rank = ?";
                param.add(szlReward.getRewardRank());
            }
            if(szlReward.getRewardTime() != null){
                sql += " and reward_time = ?";
                param.add(szlReward.getRewardTime());
            }
            if(szlReward.getRewardProof() != null){
                sql += " and reward_proof like \"%\"?\"%\"";
                param.add(szlReward.getRewardProof());
            }
        }
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            rs = psmt.executeQuery();
            while (rs.next()){
                SzlReward szlReward1 = new SzlReward();
                szlReward1.setRewardId(rs.getLong(1));
                szlReward1.setRewardName(rs.getString(2));
                szlReward1.setType(rs.getInt(3));
                szlReward1.setRewardLevel(rs.getString(4));
                szlReward1.setRewardGrade(rs.getString(5));
                szlReward1.setRewardRank(rs.getInt(6));
                szlReward1.setRewardTime(rs.getDate(7));
                szlReward1.setRewardProof(rs.getString(8));
                list.add(szlReward1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return list ;
    }
}
